USE [TBbilling]
GO
/****** Object:  StoredProcedure [dbo].[getProcessDate]    Script Date: 02/06/2015 15:43:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[getProcessDate]
as 

begin
 set nocount on
Declare @LastProcessDate datetime
Declare @NewProcessEndDate datetime
Declare @NewTransferStartDate datetime


--truncate table tbbilling.dbo.TransferAudit
truncate table dbo.NewDemographics

select @NewProcessEndDate = GETDATE()
select @NewTransferStartDate = @NewProcessEndDate

select @LastProcessDate =  MAX(processEndDate)
from dbo.TransferAudit



If @LastProcessDate is null
Begin 
 set @LastProcessDate = '1/1/1990'
end

Insert into dbo.transferAudit ( transferStartDate,processStartDate, processEndDate)
values (@NewTransferStartDate,@LastProcessDate,@NewProcessEndDate)

select @LastProcessDate 'BeginDate'
,@NewProcessEndDate 'EndDate'
,@NewTransferStartDate 'TransferStartDate'

/*****
Updated the records to include for trubridge 
*/
declare @Tranactiondate datetime2
declare @EndtransactionDate datetime2

set @Tranactiondate = @LastProcessDate
set @EndtransactionDate = @NewProcessEndDate
create table #NewTransactions (billID int, episodeID int, transaction_date datetime2)

insert into #Newtransactions
Select BillID, EpisodeID, Transaction_Date
from Angus.dbo.shr_transactions
where Description like '%to TI%'
and Transaction_Date between @Tranactiondate and @EndtransactionDate

union
Select BillID, EpisodeID, Transaction_Date
from Angus.dbo.shr_transactions
where Description like '%to TB%'
and Transaction_Date between @Tranactiondate and @EndtransactionDate
union
Select BillID, EpisodeID, Transaction_Date
from Angus.dbo.shr_transactions
where Description like '%to TA%'
and Transaction_Date between @Tranactiondate and @EndtransactionDate
union
Select BillID, EpisodeID, Transaction_Date
from Angus.dbo.shr_transactions
where Description like '%to TM%'
and Transaction_Date between @Tranactiondate and @EndtransactionDate
union
Select BillID, EpisodeID, Transaction_Date
from Angus.dbo.shr_transactions
where Description like '%to TR%'
and Transaction_Date between @Tranactiondate and @EndtransactionDate




insert into dbo.Newdemographics 

select billid, episodeID, transaction_date
from (
MERGE PatientTransfer AS PT
USING #NewTransactions AS dd
on (pt.billid = dd.billId
and pt.episodeID = dd.episodeID
and pt.transaction_date = dd.transaction_date)
WHEN NOT MATCHED THEN
    INSERT (billid, episodeID, transaction_date)
 values (dd.billid,dd.episodeid,dd.transaction_date)        
OUTPUT $action, Inserted.billid, Inserted.episodeid, Inserted.transaction_Date )
 AS Changes (Action, BillID, episodeID, transaction_date) WHERE Action = 'Insert';


drop table #NewTransactions


/*****************************************************************/

create table #DeleteTransactions (billID int, episodeID int, transaction_date datetime2)



Insert into #DeleteTransactions
Select BillID, EpisodeID, Transaction_Date
from Angus.dbo.shr_transactions
where Description like '%From TI%'
and Transaction_Date between @Tranactiondate and @EndtransactionDate

union
Select BillID, EpisodeID, Transaction_Date
from Angus.dbo.shr_transactions
where Description like '%From TB%'
and Transaction_Date between @Tranactiondate and @EndtransactionDate
union
Select BillID, EpisodeID, Transaction_Date
from Angus.dbo.shr_transactions
where Description like '%From TA%'
and Transaction_Date between @Tranactiondate and @EndtransactionDate
union
Select BillID, EpisodeID, Transaction_Date
from Angus.dbo.shr_transactions
where Description like '%From TM%'
and Transaction_Date between @Tranactiondate and @EndtransactionDate
union
Select BillID, EpisodeID, Transaction_Date
from Angus.dbo.shr_transactions
where Description like '%From TR%'
and Transaction_Date between @Tranactiondate and @EndtransactionDate




MERGE PatientTransfer AS PT
USING #DeleteTransactions AS dd
on (pt.billid = dd.billId
and pt.episodeID = dd.episodeID
)
WHEN MATCHED and PT.transaction_date < dd.transaction_date
    then Delete
    
    ;
        


drop table #DeleteTransactions


set nocount off
 
end
GO
